package sm.test;

import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import oracle.jdbc.driver.OracleDriver;

public class FullTableCopy2 {

	public static void main(String[] args) {
		try {
			System.out.println("begin full table copy of cm3r: "
					+ System.currentTimeMillis());
			DriverManager.deregisterDriver(new OracleDriver());
			Connection cn = DriverManager.getConnection(
					"jdbc:oracle:thin:@16.157.129.142:1521:o11r2upg",
					"SC_OWNER", "openview");
			Statement st = cn.createStatement();

			st.execute("INSERT INTO CM3RA1_NEW (\"NUMBER\", RECORD_NUMBER, REVIEWER_CLASS) SELECT NUMBERPRGN,RECORD_NUMBER,REVIEWER_CLASS FROM CM3RA1");
			System.out
					.println("CM3RA1 complete: " + System.currentTimeMillis());

			st.execute("INSERT INTO CM3RA2_NEW (\"NUMBER\", RECORD_NUMBER, CURRENT_PENDING_GROUPS) SELECT NUMBERPRGN,RECORD_NUMBER,CURRENT_PENDING_GROUPS FROM CM3RA2");
			System.out
					.println("CM3RA2 complete: " + System.currentTimeMillis());

			st.execute("ALTER TABLE CM3RM1 ADD SVC_OPTIONS_NEW BLOB");
			st.execute("UPDATE CM3RM1 SET SVC_OPTIONS_NEW=empty_blob()");

			System.out
					.println("CM3RM1 complete: " + System.currentTimeMillis());

			ResultSet rs = st
					.executeQuery("SELECT NUMBERPRGN, ASSETS FROM CM3RM1 where dbms_lob.getlength(ASSETS) > 0");

			PreparedStatement ps = cn
					.prepareStatement("INSERT INTO CM3RA3_NEW(\"NUMBER\", RECORD_NUMBER, ASSETS) values (?, ?, ?)");
			while (rs.next()) {
				String number = rs.getString(1);
				Clob assetClob = rs.getClob(2);
				int length = (int) assetClob.length();
				char[] chars = new char[length];
				Reader reader = assetClob.getCharacterStream();
				reader.read(chars);
				String assetStr = new String(chars);

				String[] assets = assetStr.split("\\n");
				for (int i = 0; i < assets.length; i++) {
					ps.setString(1, number);
					ps.setInt(2, i + 1);
					ps.setString(3, assets[i]);
					ps.addBatch();
				}
			}
			ps.executeBatch();
			System.out
					.println("CM3RA3 complete: " + System.currentTimeMillis());

			ps.close();
			cn.commit();
			st.close();
			cn.close();

			System.out.println("end full table copy of cm3r: "
					+ System.currentTimeMillis());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
